﻿using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text;
using System.Data;

namespace WebShopData
{
    public static class WebShopDal
    {
        public static string ApplicationName { get; set; }
        public static int ConnectionTimeout { get; set; }

        public static string ConnectionString
        {
            get
            {
                var connectionString = ConfigurationManager.ConnectionStrings["theWebShop"].ToString();
                var newConnectionString = new SqlConnectionStringBuilder(connectionString);
                newConnectionString.ApplicationName = ApplicationName ?? newConnectionString.ApplicationName;
                newConnectionString.ConnectTimeout = (ConnectionTimeout > 0) ? ConnectionTimeout : newConnectionString.ConnectTimeout;
                return newConnectionString.ToString();
            }
        }

        public static SqlConnection GetConnection()
        {
            var conn = new SqlConnection(ConnectionString.ToString());
            conn.Open();
            return conn;
        }

        public static int AddOrderToDb(Order order)
        {
            using (var connection = GetConnection())
            {
                int orderId;
                int affectedRows = 0;

                StringBuilder sqlQuery = new StringBuilder();
                sqlQuery.Append("INSERT INTO [dbo].[OrderHead] ([UserID] ,[Address] ,[ZipCode] ,[City] ,[Processed]) ");
                sqlQuery.Append("VALUES (@UserID ,@Address ,@ZipCode ,@City ,@Processed)");

                using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                {
                    command.Parameters.Add("UserID", System.Data.SqlDbType.Int).Value = order.UserID;
                    command.Parameters.Add("Address", System.Data.SqlDbType.VarChar, 35).Value = order.Address;
                    command.Parameters.Add("ZipCode", System.Data.SqlDbType.Int).Value = order.ZipCode;
                    command.Parameters.Add("City", System.Data.SqlDbType.VarChar, 30).Value = order.City;
                    command.Parameters.Add("Processed", System.Data.SqlDbType.Bit).Value = 0;
                    affectedRows += command.ExecuteNonQuery();
                }

                if (affectedRows == 0)
                    throw new DataException("Order failed to save.");

                SqlDataAdapter adapter = new SqlDataAdapter("SELECT TOP 1 OrderID FROM [dbo].[OrderHead] ORDER BY OrderID DESC", connection);
                DataSet set = new DataSet();
                adapter.Fill(set);

                orderId = int.Parse(set.Tables[0].Rows[0]["OrderID"].ToString());

                sqlQuery.Clear();
                sqlQuery = new StringBuilder("INSERT INTO [dbo].[OrderRow] ([OrderID] ,[Quantity] ,[ArticleID]) ");
                sqlQuery.Append("VALUES (@OrderID ,@Quantity ,@ArticleID)");

                using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                {
                    command.Parameters.Add("OrderID", SqlDbType.Int).Value = orderId;
                    command.Parameters.Add("Quantity", SqlDbType.Int);
                    command.Parameters.Add("ArticleID", SqlDbType.Int);

                    foreach (OrderRow orderRow in order.OrderRows)
                    {
                        command.Parameters["Quantity"].Value = orderRow.Quantity;
                        command.Parameters["ArticleID"].Value = orderRow.ArticleID;
                        affectedRows += command.ExecuteNonQuery();
                    }
                }

                return affectedRows;
            }
        }

        public static int CreateNewUser(User user)
        {
            var sqlQuery = new StringBuilder();
            sqlQuery.Append("INSERT INTO [dbo].[Users]([Username],[Password]) ");
            sqlQuery.Append("VALUES (@UserName, @Password)");
            using (var connection = GetConnection())
            {
                using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                {
                    command.Parameters.Add("UserName", SqlDbType.VarChar, 30).Value = user.UserName;
                    command.Parameters.Add("Password", SqlDbType.VarChar, 30).Value = user.Password;

                    int affectedRows = command.ExecuteNonQuery();
                    return affectedRows;
                }
            }
        }

        public static User ReadUser(int userId)
        {
            var sqlQuery = new StringBuilder();
            sqlQuery.Append("SELECT [UserID],[Username],[Password] ");
            sqlQuery.Append("FROM [dbo].[Users]");
            sqlQuery.Append("WHERE UserID = @UserID");
            using (var connection = GetConnection())
            {
                using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                {
                    command.Parameters.Add("UserID", SqlDbType.Int).Value = userId;

                    var adapter = new SqlDataAdapter(command.CommandText, connection);
                    var dataset = new DataSet();
                    adapter.Fill(dataset);
                    var table = dataset.Tables[0];
                    var user = new User(
                        userId: int.Parse(table.Rows[0]["UserID"].ToString()),
                        userName: table.Rows[0]["Username"].ToString(),
                        password: table.Rows[0]["Password"].ToString());

                    return user;
                }
            }
        }

        public static Article ReadArticle(int articleId)
        {
            var articles = GetAllArticles();
            Article outArticle = null;
            foreach (DataRow article in articles.Tables[0].Rows)
            {
                if (article["ArticleID"].ToString() == articleId.ToString())
                {
                    outArticle = new Article(
                        articleid: articleId,
                        name: article["Text"].ToString(),
                        unitPrice: double.Parse(article["UnitPrice"].ToString()));
                }
            }
            if (outArticle == null)
                throw new NullReferenceException("Article not found.");
            return outArticle;
        }

        public static Article[] ReadArticles(params int[] articleIds)
        {
            var articles = GetAllArticles();
            var outArticles = new Article[articleIds.Length];
            for (int i = 0; i < outArticles.Length; i++)
            {
                foreach (DataRow article in articles.Tables[0].Rows)
                {
                    if (article["ArticleID"].ToString() == articleIds[i].ToString())
                    {
                        outArticles[i] = new Article(
                            articleid: articleIds[i],
                            name: article["Text"].ToString(),
                            unitPrice: double.Parse(article["UnitPrice"].ToString()));
                    }
                }
            }

            if (outArticles == null)
                throw new NullReferenceException("Article not found.");

            return outArticles;
        }

        public static DataSet GetAllArticles()
        {
            using (SqlConnection connection = GetConnection())
            {
                string sqlQuery = @"SELECT [ArticleID],[Text],[UnitPrice] FROM [Articles]";

                SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, connection);
                DataSet set = new DataSet();

                adapter.Fill(set, "Articles");
                return set;
            }
        }

        public static DataSet GetAllOrderHeads()
        {
            using (SqlConnection connection = GetConnection())
            {
                string sqlQuery = @"SELECT [OrderID],[UserID],[Address],[ZipCode],[City],[Processed] FROM [dbo].[OrderHead]";

                SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, connection);
                DataSet set = new DataSet();

                adapter.Fill(set, "OrderHeads");
                return set;
            }
        }

        public static DataSet GetOrderrow(int orderHeadId)
        {
            var query = new StringBuilder();
            query.Append("SELECT [OrderRowID],[OrderID],[Quantity],[ArticleID] ");
            query.Append("FROM [dbo].[OrderRow] WHERE [OrderID] = @OrderHeadID");

            using (var conn = WebShopDal.GetConnection())
            {
                using (var command = new SqlCommand(query.ToString(), conn))
                {
                    command.Parameters.Add("OrderHeadID", SqlDbType.Int).Value = orderHeadId;
                    var adapter = new SqlDataAdapter(command.CommandText, conn);
                    var set = new DataSet();

                    adapter.Fill(set);
                    return set;
                }
            }
        }

        public static int InsertArticle(string text, double unitPrice)
        {
            using (var connection = GetConnection())
            {
                var sqlQuery = new StringBuilder(@"INSERT INTO [dbo].[Articles] ([Text],[UnitPrice]) ");
                sqlQuery.Append(@"VALUES (@Text,@UnitPrice)");

                using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                {
                    command.Parameters.Add("Text", SqlDbType.Text).Value = text;
                    command.Parameters.Add("UnitPrice", SqlDbType.Money).Value = unitPrice;
                    int affectedRows = command.ExecuteNonQuery();
                    return affectedRows;
                }
            }
        }

        public static DataSet GetOrderOverviewSingleOrder(int orderId)
        {
            var query = new StringBuilder();
            query.Append(
                @"SELECT [Username],[Address],[City],[ZipCode],[OrderID],[Processed],[Quantity],[Text],[UnitPrice] ");
            query.Append(@"FROM [OrderView] WHERE [OrderID] = " + orderId);

            using (var conn = GetConnection())
            {
                var command = new SqlCommand(query.ToString());
                var adapter = new SqlDataAdapter(command.CommandText, conn);
                var set = new DataSet();
                adapter.Fill(set);

                return set;
            }
        }

        public static bool AuthenticateLoginValues(string userName, string password, out int userId)
        {
            using (SqlConnection connection = GetConnection())
            {
                StringBuilder sqlQuery = new StringBuilder("SELECT * ");
                sqlQuery.Append("FROM [Users]");
                sqlQuery.Append("WHERE [Username] = @Username ");
                sqlQuery.Append("AND [Password] = @Password");
                using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                {
                    command.Parameters.Add("Username", SqlDbType.VarChar, 30).Value = userName;
                    command.Parameters.Add("Password", SqlDbType.VarChar, 30).Value = password;

                    var adapter = new SqlDataAdapter(command);
                    var dataSet = new DataSet();
                    adapter.Fill(dataSet);

                    if (dataSet.Tables[0].Rows.Count > 0)
                    {
                        userId = int.Parse(dataSet.Tables[0].Rows[0]["UserID"].ToString());
                        return true;

                    }
                }
            }

            userId = 0;
            return false;
        }

        public static int SetOrderToProcessed(bool processed, int orderid)
        {
            int processedInt = (processed) ? 1 : 0;
            var query = new StringBuilder();
            query.Append("UPDATE [dbo].[OrderHead] SET [Processed] = @DoneProcessed ");
            query.Append("WHERE OrderID = @orderid");
            using (var conn = GetConnection())
            {
                using (var command = new SqlCommand(query.ToString(),conn))
                {
                    command.Parameters.Add("DoneProcessed", SqlDbType.Int).Value = processedInt;
                    command.Parameters.Add("orderid", SqlDbType.Int).Value = orderid;

                    return command.ExecuteNonQuery();
                }
            }
        }
    }
}

